﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_SMS_GetMyAllMsg]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_SMS_GetMyAllMsg];
GO
CREATE PROCEDURE [dbo].[sproc_SMS_GetMyAllMsg]
    @Username nvarchar(50)

/*

===================================================
功能:    获取我的所有短讯
参数:
    @Username 用户名
    
        
返回值:
    记录集
===================================================

*/
AS

SET NOCOUNT ON

--判断是否有未读新的短消息
SELECT 
     (SELECT TOP 1 RealName 
                    FROM 
                        UDS_Staff 
                    WHERE     UDS_Staff.Staff_Name=Sender)
            AS SenderRealName,Content,SendTime,ID,isread,Sender,Receiver As ReceiverRealName,m.type,
            CASE m.type
             WHEN 1   THEN '站内短讯'
             WHEN 2   THEN '站内手机短讯'
             WHEN 3   THEN '站外手机短讯'  
            ELSE ''
            END AS typeDetail,r.Receiver
            FROM 
                UDS_SMS_Msg m,
                UDS_SMS_Receiver r 
            WHERE
                r.Receiver = @Username
                and m.ID=r.MsgID 
            ORDER BY
                    sendtime
            DESC
            
SET NOCOUNT OFF